﻿Imports System.Data.SqlClient

Module PruebaSinConexion

    Sub Main()
        Dim conexion As New SqlConnection("Server=USUARIO-PC\SQLSERVER;Database=Video;Trusted_Connection=True;")
        conexion.Open()
        'ExecuteReader(conexion)
        'ExecuteNonQuery(conexion)
        'ExecuteScalar(conexion)
        ExecuteStoredProcedure(conexion)
        conexion.Close()
        Console.ReadKey()
    End Sub

    Private Sub ExecuteReader(conexion As SqlConnection)
        Dim comando As New SqlCommand("Select * From Socio;")
        comando.CommandType = CommandType.Text
        comando.Connection = conexion
        Dim lector As SqlDataReader = comando.ExecuteReader()

        Console.WriteLine("{0} - {1}", lector.GetName(0), lector.GetName(1))
        While (lector.Read)
            Console.WriteLine(lector.Item(0).ToString() & " - " & lector.Item(1).ToString())
        End While
    End Sub

    Private Sub ExecuteNonQuery(conexion As SqlConnection)
        Dim comando As New SqlCommand("Insert into Socio(dni, nombre, apellido, domicilio, email, telefono) values(51651651, 'borrar', 'borrar', 'borrar', 'borrar', 'borrar');")
        comando.CommandType = CommandType.Text
        comando.Connection = conexion

        Dim numeroDeFilasAfectadas As Integer
        Try
            numeroDeFilasAfectadas = comando.ExecuteNonQuery()
            Console.WriteLine("Número de filas afectada: {0}", numeroDeFilasAfectadas)
        Catch ex As Exception
            Console.WriteLine("Ha ocurrido un error pues no se ha insertado el valor en la base de datos")
        End Try
    End Sub

    Private Sub ExecuteScalar(conexion As SqlConnection)
        Dim comando As New SqlCommand("select count(genero_id) from Pelicula where genero_id = 9")
        comando.CommandType = CommandType.Text
        comando.Connection = conexion

        Dim numeroDePeliculasDeAventura As Integer
        numeroDePeliculasDeAventura = CInt(comando.ExecuteScalar())
        Console.WriteLine("Número de películas de aventura: {0}", numeroDePeliculasDeAventura)
    End Sub

    Private Sub ExecuteStoredProcedure(conexion As SqlConnection)
        Dim comando As New SqlCommand("dbo.sp_numero_alquileres_vencidos", conexion)
        comando.CommandType = CommandType.StoredProcedure
        Dim parametro As New SqlParameter("@dni", SqlDbType.VarChar, 50)
        parametro.Value = "21314545"
        comando.Parameters.Add(parametro)

        Dim output As SqlParameter = comando.Parameters.AddWithValue("@salida", "")
        output.SqlDbType = SqlDbType.Int
        output.Direction = ParameterDirection.Output

        comando.ExecuteNonQuery()
        Console.WriteLine("Número de películas que adeuda el usuario X: {0}", comando.Parameters("@salida").SqlValue)
    End Sub

End Module
